﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DAL;

namespace BLL
{
    public class KhachHang_BLL
    {
        DataTable dt = new DataTable();
        DataService DB = new DataService();
        public int KhachHang_DangNhap(string TenDN, string MatKhau, DateTime DNLanCuoi, int Status)
        {
            int reVal = 0;
            string sql =
                 "SELECT COUNT([CustommerID]) as Num " +
                  "FROM [DBShoppingCart_New].[dbo].[tbl_Custommers] " +
                  "where LoginName = @LoginName and Password = @Password and Active=@Active ";

            using (SqlConnection conn = new SqlConnection(DB.ChuoiKetNoi))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);

                cmd.Parameters.Add("@LoginName", SqlDbType.VarChar);
                cmd.Parameters["@LoginName"].Value = TenDN;

                cmd.Parameters.Add("@Password", SqlDbType.VarChar);
                cmd.Parameters["@Password"].Value = MatKhau;

                cmd.Parameters.Add("@Active", SqlDbType.Int);
                cmd.Parameters["@Active"].Value = Status;

                try
                {
                    conn.Open();
                    reVal = (int)cmd.ExecuteScalar();                    
                }
                catch (Exception ex)
                {
                    
                }
            }
            return reVal;

            //SqlParameter[] arrParam ={
            //    new SqlParameter("@TenDN", SqlDbType.VarChar),
            //    new SqlParameter("@MatKhau", SqlDbType.VarChar),
            //    new SqlParameter("@DNLanCuoi", SqlDbType.DateTime),};
            //arrParam[0].Value = TenDN;
            //arrParam[1].Value = MatKhau;
            //arrParam[2].Value = DNLanCuoi;
            //dt = DB.ViewTableParamStore("KhachHang_DangNhap", arrParam);
            //return dt;            
        }
        public DataTable KhachHang_ViewTable(int Active, string TenDN)
        {
            string Query =
            "SELECT [CustommerID]\n" +
            "      ,[LoginName]\n" +
            "      ,[Password]\n" +
            "      ,[Email]\n" +
            "      ,[FullName]\n" +
            "      ,[PhoneNumber]\n" +
            "      ,[Address]\n" +
            "      ,[City]\n" +
            "      ,[Province]\n" +
            "      ,[Sex]\n" +
            "      ,[Birthday]\n" +
            "      ,[MoreInfo]\n" +
            "      ,[Created]\n" +
            "      ,[CountLogin]\n" +
            "      ,[LastLogin]\n" +
            "      ,[Active]\n" +
            "  FROM [DBShoppingCart_New].[dbo].[tbl_Custommers] " +
            "  Where Active=@Active and " +
            "  LoginName = @LoginName ";

            SqlParameter[] arrParam ={
                    new SqlParameter("@Active", SqlDbType.Int),
                    new SqlParameter("@LoginName", SqlDbType.VarChar)};
            arrParam[0].Value = Active;
            arrParam[1].Value = TenDN;
            dt = DB.ViewTableParam(Query,arrParam);
            return dt;
        }

        public bool CheckExitsUser(string Email, string LoginName)
        {
            int reVal = 0;
            string sql =


                 "SELECT COUNT([CustommerID]) as Num " +
                  "FROM [DBShoppingCart_New].[dbo].[tbl_Custommers] " +
                  "where (LoginName = @LoginName or Email = @Email ) and AccType != 0 ";

            using (SqlConnection conn = new SqlConnection(DB.ChuoiKetNoi))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);

                cmd.Parameters.Add("@LoginName", SqlDbType.VarChar);
                cmd.Parameters["@LoginName"].Value = LoginName;

                cmd.Parameters.Add("@Email", SqlDbType.VarChar);
                cmd.Parameters["@Email"].Value = Email;

                try
                {
                    conn.Open();
                    reVal = (int)cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {

                }
            }
            return reVal > 0 ? true : false;
        }

        public void UpdateCustomer(string CustomerId, 
                              string HoTen, string DienThoai, string DiaChi,
                              string GioiTinh, DateTime NgaySinh,
                              ref string error)
        {

            string Query = "UPDATE [DBShoppingCart_New].[dbo].[tbl_Custommers] " +
            "   SET " +
            "       [FullName] = @FullName " +
            "      ,[PhoneNumber] = @PhoneNumber" +
            "      ,[Address] = @Address " +
            "      ,[Sex] = @Sex" +
            "      ,[Birthday] = @Birthday " +            
            " WHERE [CustommerID] = @CustommerID ";

            try
            {
                SqlConnection sqlConn = new SqlConnection(DB.ChuoiKetNoi);
                SqlCommand sqlComm = new SqlCommand();
                sqlComm = sqlConn.CreateCommand();
                sqlComm.CommandText = @Query;                                              

                sqlComm.Parameters.Add("@FullName", SqlDbType.NVarChar);
                sqlComm.Parameters["@FullName"].Value = HoTen;

                sqlComm.Parameters.Add("@PhoneNumber", SqlDbType.VarChar);
                sqlComm.Parameters["@PhoneNumber"].Value = DienThoai;

                sqlComm.Parameters.Add("@Address", SqlDbType.NVarChar);
                sqlComm.Parameters["@Address"].Value = DiaChi;

                sqlComm.Parameters.Add("@Sex", SqlDbType.VarChar);
                sqlComm.Parameters["@Sex"].Value = GioiTinh;

                sqlComm.Parameters.Add("@Birthday", SqlDbType.DateTime);
                sqlComm.Parameters["@Birthday"].Value = NgaySinh;
                
                sqlComm.Parameters.Add("@CustommerID", SqlDbType.VarChar);
                sqlComm.Parameters["@CustommerID"].Value = CustomerId;

                sqlConn.Open();
                sqlComm.ExecuteNonQuery();
                sqlConn.Close();

            }
            catch (Exception ex)
            {
                error = "ERROR: " + ex.Message;
            }
        }


        public void Admin_UpdateCustomer(string CustomerId,
                             string HoTen, string DienThoai, string DiaChi,
                             string GioiTinh, DateTime NgaySinh,int Active,string Reason,
                                string Email                             ,bool resetPass, string newPass)
        {
            string subQuery = string.Empty;
            if (resetPass) {
                subQuery = "      ,[Password] = @Password";
            }

            string Query = "UPDATE [DBShoppingCart_New].[dbo].[tbl_Custommers] " +
            "   SET " +
            "       [FullName] = @FullName " +
            "      ,[PhoneNumber] = @PhoneNumber" +            
            "      ,[Email] = @Email " +
            "      ,[Active] = @Active " +            
            "      ,[Address] = @Address " +
            "      ,[Sex] = @Sex" +
            "      ,[LockedReason] = @LockedReason" +
            "      ,[Birthday] = @Birthday " +       
                    subQuery +
            " WHERE [CustommerID] = @CustommerID ";

            try
            {
                SqlConnection sqlConn = new SqlConnection(DB.ChuoiKetNoi);
                SqlCommand sqlComm = new SqlCommand();
                sqlComm = sqlConn.CreateCommand();
                sqlComm.CommandText = @Query;

                sqlComm.Parameters.Add("@FullName", SqlDbType.NVarChar);
                sqlComm.Parameters["@FullName"].Value = HoTen;

                sqlComm.Parameters.Add("@PhoneNumber", SqlDbType.VarChar);
                sqlComm.Parameters["@PhoneNumber"].Value = DienThoai;

                sqlComm.Parameters.Add("@Address", SqlDbType.NVarChar);
                sqlComm.Parameters["@Address"].Value = DiaChi;

                sqlComm.Parameters.Add("@Sex", SqlDbType.VarChar);
                sqlComm.Parameters["@Sex"].Value = GioiTinh;

                sqlComm.Parameters.Add("@Birthday", SqlDbType.DateTime);
                sqlComm.Parameters["@Birthday"].Value = NgaySinh;

                sqlComm.Parameters.Add("@CustommerID", SqlDbType.VarChar);
                sqlComm.Parameters["@CustommerID"].Value = CustomerId;                
                

                sqlComm.Parameters.Add("@Email", SqlDbType.VarChar);
                sqlComm.Parameters["@Email"].Value = Email;

                sqlComm.Parameters.Add("@Active", SqlDbType.Int);
                sqlComm.Parameters["@Active"].Value = Active;

                sqlComm.Parameters.Add("@LockedReason", SqlDbType.NVarChar);
                sqlComm.Parameters["@LockedReason"].Value = Reason;

                if (resetPass) {
                    sqlComm.Parameters.Add("@Password", SqlDbType.VarChar);
                    sqlComm.Parameters["@Password"].Value = newPass;
                }

                sqlConn.Open();
                sqlComm.ExecuteNonQuery();
                sqlConn.Close();

            }
            catch (Exception ex)
            {
                
            }
        }

        public void AddNewCustomer(int Active, string TenDN, string MatKhau,
                              string Email, string HoTen, string DienThoai, string DiaChi,
                              string GioiTinh, DateTime NgaySinh, string ThongTinThem, DateTime NgayDangKi,
                              int TinhTrangID,int AccType, ref string error)
        {

            string Query = "INSERT INTO [DBShoppingCart_New].[dbo].[tbl_Custommers]\n" +
            "           ( [CustommerID]" +
            "           ,[LoginName]\n" +
            "           ,[Password]\n" +
            "           ,[Email]\n" +
            "           ,[FullName]\n" +
            "           ,[PhoneNumber]\n" +
            "           ,[Address]\n" +                       
            "           ,[Sex]\n" +
            "           ,[Birthday]\n" +            
            "           ,[Created]\n" +            
            "           ,[Active]\n" +
            "           ,[AccType])\n" +

            "VALUES\n" +
            "          ( @CustommerID" +
            "           ,@LoginName " +
            "           ,@Password" +
            "           ,@Email" +
            "           ,@FullName" +
            "           ,@PhoneNumber" +
            "           ,@Address" +                
            "           ,@Sex" +
            "           ,@Birthday" +            
            "           ,@Created" +                
            "           ,@Active "+
            "           ,@AccType) ";

            try
            {
                SqlConnection sqlConn = new SqlConnection(DB.ChuoiKetNoi);
                SqlCommand sqlComm = new SqlCommand();
                sqlComm = sqlConn.CreateCommand();
                sqlComm.CommandText = @Query;


                sqlComm.Parameters.Add("@Active", SqlDbType.Int);
                sqlComm.Parameters["@Active"].Value = Active;

                sqlComm.Parameters.Add("@LoginName", SqlDbType.VarChar);
                sqlComm.Parameters["@LoginName"].Value = TenDN;

                sqlComm.Parameters.Add("@Password", SqlDbType.VarChar);
                sqlComm.Parameters["@Password"].Value = MatKhau;

                sqlComm.Parameters.Add("@Email", SqlDbType.VarChar);
                sqlComm.Parameters["@Email"].Value = Email;

                sqlComm.Parameters.Add("@FullName", SqlDbType.NVarChar);
                sqlComm.Parameters["@FullName"].Value = HoTen;

                sqlComm.Parameters.Add("@PhoneNumber", SqlDbType.VarChar);
                sqlComm.Parameters["@PhoneNumber"].Value = DienThoai;

                sqlComm.Parameters.Add("@Address", SqlDbType.NVarChar);
                sqlComm.Parameters["@Address"].Value = DiaChi;

                sqlComm.Parameters.Add("@Sex", SqlDbType.VarChar);
                sqlComm.Parameters["@Sex"].Value = GioiTinh;

                sqlComm.Parameters.Add("@Birthday", SqlDbType.DateTime);
                sqlComm.Parameters["@Birthday"].Value = NgaySinh;

                sqlComm.Parameters.Add("@Created", SqlDbType.DateTime);
                sqlComm.Parameters["@Created"].Value = NgayDangKi;

                sqlComm.Parameters.Add("@CustommerID", SqlDbType.VarChar);
                sqlComm.Parameters["@CustommerID"].Value = System.Guid.NewGuid().ToString();

                sqlComm.Parameters.Add("@AccType", SqlDbType.SmallInt);
                sqlComm.Parameters["@AccType"].Value = AccType;

                sqlConn.Open();
                sqlComm.ExecuteNonQuery();
                sqlConn.Close();

            }
            catch (Exception ex) {
                error = "ERROR: " + ex.Message;
            }
        }
       

        [AjaxPro.AjaxMethod(AjaxPro.HttpSessionStateRequirement.ReadWrite)]
        public string Customer_DeleteItem(string CustommerID)
        {
            string reV = string.Empty;
            try
            {
                string Query =

                        " Delete from tbl_Order where CustommerID=@CustommerID ; " +

                        " Delete from tbl_OrderDetail where OrderID in " +
                        " (select OrderID from tbl_Order where CustommerID=@CustommerID ) ;" +

                        " delete from tbl_Custommers " +
                                " Where CustommerID=@CustommerID;";

                SqlParameter[] arrParam ={
                    new SqlParameter("@CustommerID", SqlDbType.VarChar)};

                arrParam[0].Value = CustommerID;
                DB.ExeCuteSqlParam(Query, arrParam);

            }
            catch (Exception ex)
            {
                reV = "ERROR: " + ex.Message;
            }
            return reV;
        }

        

        public void KhachHang_EditBill(string TenDN, string HoTen, string DienThoai, string DiaChi, string ThanhPho, string Tinh,
                              string GioiTinh, ref string error)
        {
            SqlParameter[] arrParam ={                
                new SqlParameter("@TenDN", SqlDbType.VarChar),                    
                new SqlParameter("@HoTen",SqlDbType.NVarChar),
                new SqlParameter("@DienThoai",SqlDbType.VarChar),                
                new SqlParameter("@DiaChi", SqlDbType.NVarChar),   
                 new SqlParameter("@ThanhPho", SqlDbType.NVarChar),
                  new SqlParameter("@Tinh", SqlDbType.NVarChar),
                new SqlParameter("@GioiTinh", SqlDbType.VarChar),                   
                new SqlParameter("@error",SqlDbType.NVarChar,200)};
            arrParam[0].Value = TenDN;
            arrParam[1].Value = HoTen;
            arrParam[2].Value = DienThoai;
            arrParam[3].Value = DiaChi;
            arrParam[4].Value = ThanhPho;
            arrParam[5].Value = Tinh;
            arrParam[6].Value = GioiTinh;
            arrParam[7].Direction = ParameterDirection.Output;//Luu y cho nay           
            DB.ExeCuteStoreParam("KhachHang_EditBill", arrParam);
            error = (arrParam[7].Value.ToString() + "" != "" ? arrParam[7].Value.ToString() : "");
        }
        public void KhachHang_Xoa(string TenDN, ref string error)
        {
            SqlParameter[] arrParam ={
                new SqlParameter("@TenDN", SqlDbType.VarChar),
                  new SqlParameter("@error",SqlDbType.NVarChar,200)};
            arrParam[0].Value = TenDN;
            arrParam[1].Direction = ParameterDirection.Output;//Luu y cho nay   
            DB.ExeCuteStoreParam("KhachHang_Delete", arrParam);
            error = (arrParam[1].Value.ToString() + "" != "" ? arrParam[1].Value.ToString() : "");
        }
        public void KhachHang_DoiMatKhau(int Active, string TenDN, string MatKhauCu, string MatKhauMoi, string Email, ref string error)
        {
            SqlParameter[] arrParam ={
                new SqlParameter("@Active", SqlDbType.Int),
                new SqlParameter("@TenDN", SqlDbType.VarChar),
                new SqlParameter("@MatKhauCu", SqlDbType.VarChar),
                new SqlParameter("@MatKhauMoi", SqlDbType.VarChar),  
                new SqlParameter("@Email", SqlDbType.VarChar), 
                new SqlParameter("@error",SqlDbType.NVarChar,200)};
            arrParam[0].Value = Active;
            arrParam[1].Value = TenDN;
            arrParam[2].Value = MatKhauCu;
            arrParam[3].Value = MatKhauMoi;
            arrParam[4].Value = Email;
            arrParam[5].Direction = ParameterDirection.Output;//Luu y cho nay           
            DB.ExeCuteStoreParam("KhachHang_DoiMatKhau", arrParam);
            error = (arrParam[5].Value.ToString() + "" != "" ? arrParam[5].Value.ToString() : "");

        }

        public DataTable GetCustomerById(Guid ID)
        {
            string Query =
                 "   Select " +
                 " CustommerID" +
                 ",LoginName" +
                 ",Password" +
                 ",Email" +
                 ",FullName" +
                 ",PhoneNumber" +
                 ",Address" +
                 ",Sex" +
                 ",Birthday" +
                 ",MoreInfo" +
                 ",Created" +
                 ",CountLogin" +
                 ",LastLogin " +
                 ",LockedReason " +
                 ",Active, " +
                 "	Case Sex When 'Nu' Then N'0' When 'Nam' Then N'1' End as _Sex, " +
                 "	Case Active When 0 Then N'Khóa' When 1 Then N'Hoạt động' End as _Active   " +
                 " From tbl_Custommers " +
                        "	Where CustommerID = @ItemID ";
            SqlParameter[] arrParam ={
                    new SqlParameter("@ItemID", SqlDbType.UniqueIdentifier)};
            arrParam[0].Value = ID;
            DataTable dtResult = new DataTable();
            dtResult = DB.ViewTableParam(Query, arrParam);
            return dtResult;
        }


        public void DeleteCustomerById(Guid ItemID)
        {
            string Query = "Delete From tbl_Custommers" +
                        " Where CustommerID=@ItemID";
            SqlParameter[] arrParam ={
                    new SqlParameter("@ItemID", SqlDbType.UniqueIdentifier)};
            arrParam[0].Value = ItemID;
            DB.ExeCuteSqlParam(Query, arrParam);
        }

        public DataTable Customer_Search(string Keyword)
        {
            string Query = 
                "   Select " +
                 " CustommerID" +
                 ",LoginName" +
                 ",Password" +
                 ",Email" +
                 ",FullName" +
                 ",PhoneNumber" +
                 ",Address" +
                 ",Sex" +
                 ",Birthday" +
                 ",MoreInfo" +
                 ",Created" +
                 ",CountLogin" +
                 ",LastLogin " +
                 ",LockedReason " +
                 ",Active, " +
                 "	Case Sex When 'Nu' Then N'0' When 'Nam' Then N'1' End as _Sex, " +
                 "	Case Active When 0 Then N'Khóa' When 1 Then N'Hoạt động' End as _Active   " +
                 " From tbl_Custommers " +
                        "Where LoginName like N'%'+@Keyword+'%' or Email like N'%'+@Keyword+'%' or FullName like N'%'+@Keyword+'%' " +
                        "Order By Created";
            SqlParameter[] arrParam ={
                    new SqlParameter("@Keyword", SqlDbType.NVarChar)};
            arrParam[0].Value = Keyword;
            DataTable dtResult = new DataTable();
            dtResult = DB.ViewTableParam(Query, arrParam);
            return dtResult;
        }

        public DataTable TimKiem(string Keyword, int TinhTrangID)
        {
            string ChuoiTimKiem = "SELECT kh.TenDN,kh.HoTen, kh.DiaChi, kh.DienThoai, kh.NgayDangKi,kh.DNLanCuoi FROM tbl_KhachHang kh  where 1=1 ";
            if (Keyword.Length != 0)
                ChuoiTimKiem += "AND (kh.TenDN LIKE '%" + Keyword + "%' or kh.Email LIKE '%" + Keyword + "%'  or kh.HoTen LIKE N'%" + Keyword + "%' or kh.DiaChi LIKE N'%" + Keyword + "%' or kh.DienThoai LIKE '%" + Keyword + "%')";
            if (TinhTrangID > -1)
                ChuoiTimKiem += "AND kh.TinhTrangID ='" + TinhTrangID + "'";
            ChuoiTimKiem += "Order By kh.NgayDangKi desc";
            dt = DB.ViewTableSql(ChuoiTimKiem);
            return dt;
        }
        public string KhachHang_MaKH(string TenDN)
        {
            string MaKH;
            SqlParameter[] arrParam ={
                new SqlParameter("@TenDN", SqlDbType.VarChar)};
            arrParam[0].Value = TenDN;
            MaKH = DB.ExeCuteScalarParamStore("KhacHang_SelectMaKH", arrParam);
            return MaKH;
        }
        public string KhachHang_Count()
        {
            string Count = DB.ExeCuteScalarParamStore("KhachHang_Count").ToString();
            return Count;
        }
        public DataTable KhachHang_New()
        {
            DataTable dt = DB.ViewTableParamStore("KhachHang_New");
            return dt;
        }
    }
}
